package com.cskaoyan.batch;

import com.cskaoyan.jdbc.util.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 创建日期: 2022/06/02 15:09
 *
 * @author ciggar
 *
 * 批处理案例
 */
public class BatchDemo {

    static Connection connection;
    static int count = 1000000;

    static {
        connection = JDBCUtils.getConnection();
    }


    public static void main(String[] args) throws SQLException {

//        long t1 = System.currentTimeMillis();
//
//        insertUseForeach(count);
//
//        long t2 = System.currentTimeMillis();
//
//        insertUseStatementBatch(count);

        long t3 = System.currentTimeMillis();

        insertUserPreparedStatement(count);

        long t4 = System.currentTimeMillis();

//        System.out.println("foreach:" + (t2-t1) + " 毫秒");
//        System.out.println("statement:" + (t3-t2) + " 毫秒");
        System.out.println("preparedStatement:" + (t4-t3) + " 毫秒");

    }



    // foreach
    public static void insertUseForeach(int count) throws SQLException {

        for (int i = 0; i < count; i++) {
            // 获取Statement
            Statement statement = connection.createStatement();

            String username = "foreach:" + i;
            // 执行SQL语句
            statement.executeUpdate("insert into account values (null,'"+username+"','xxx')");

            // 关闭资源
            statement.close();

        }
    }

    // statement
    public static void insertUseStatementBatch(int count) throws SQLException {

        // 创建一个Statement
        Statement statement = connection.createStatement();

        // 可以往Statement容器中添加SQL语句
        for (int i = 0; i < count; i++) {

            String username = "statement:" + i;
            String sql = "insert into account values (null,'"+username+"','666')";
            statement.addBatch(sql);

        }

        // 执行批处理的SQL
        statement.executeBatch();

        // 关闭Statement
        statement.close();

    }


    // preparedStatement
    public static void insertUserPreparedStatement(int count) throws SQLException {

        // 获取PreparedStatement， 对SQL语句进行预编译
        PreparedStatement preparedStatement = connection.prepareStatement("insert into account values (null,?,?)");

        // insert into account values (),(),(),(),();
        // 设置参数
        for (int i = 0; i < count; i++) {
            preparedStatement.setString(1,"prepared:" + i);
            preparedStatement.setString(2,"999");

            preparedStatement.addBatch();
        }

        // 发送参数，执行SQL语句
        preparedStatement.executeBatch();

        // 关闭资源
        preparedStatement.close();
    }

}
